using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using DTO;
using System.Data;
namespace DAOLayer
{
    public class CuonSachDAO : AbStractDao
    {
        public CuonSachDAO()
        {
            this.connect();
        }


        public DataTable getMaCuonSach(int maDauSach)
        {
            string query = "select * from CuonSach where maDauSach=" + maDauSach;
            CuonSachDAO cuonsachDAO = new CuonSachDAO();
            DataTable dt = cuonsachDAO.fillDataTable(query);
            cuonsachDAO.disconnect();

            return dt;
        }

        public static DataTable getMaDauSachTheoMaCuonSachVaTinhTrang(int maCuonSach, String tinhTrang)
        {
            //Y : con
            string query = "select * from CuonSach where maCuonSach = " + maCuonSach + " and tinhTrang = '" + tinhTrang + "'";
            CuonSachDAO cuonsachDAO = new CuonSachDAO();
            DataTable dt = cuonsachDAO.fillDataTable(query);
            cuonsachDAO.disconnect();

            return dt;
        }

        public DataTable getListCuonSach()
        {
            string strQuery = "select cs.MaCuonSach,cs.TinhTrang,ds.NgonNgu,ds.Bia,ts.TenDauSach,ts.TacGia,ts.TomTat ";
            strQuery += "from CuonSach cs,DauSach ds,DauSach ts ";
            strQuery += " where cs.MaCuonSach = ds.MaCuonSach and cs.MaDauSach = ts.MaDauSach";
            CuonSachDAO dgDao = new CuonSachDAO();
            DataTable dt = dgDao.fillDataTable(strQuery);
            dgDao.disconnect();
            return dt;
        }
        public DataTable getListCuonSachByMaDauSach(int maDauSach)
        {
            string strQuery = "select * from CuonSach where maDauSach=" + maDauSach;
            CuonSachDAO dgDao = new CuonSachDAO();
            DataTable dt = dgDao.fillDataTable(strQuery);
            dgDao.disconnect();
            return dt;
        }
        public CuonSachDTO getThongTinCuonSach(int macuonsach)
        {
            string strQuery = "select cs.maCuonSach,cs.tinhTrang,ds.ngonNgu,ds.bia,ts.tenTuaSach,ts.tacGia,ts.tomTat ";
            strQuery += "from CuonSach cs,DauSach ds,TuaSach ts ";
            strQuery += " where cs.maCuonSach = " + macuonsach;
            strQuery += " and cs.maDauSach = ds.maDauSach and ds.maTuaSach = ts.maTuaSach";
            CuonSachDAO csDao = new CuonSachDAO();
            IDataReader dr = csDao.executeQuery(strQuery);
            CuonSachDTO csDto = new CuonSachDTO();

            if (dr.Read())
            {
                csDto.MaCuonSach = dr.GetInt32(0);
                csDto.TinhTrang = dr.GetString(1);
                csDto.NgonNgu = dr.GetString(2);
                csDto.Bia = dr.GetString(3);
                csDto.TuaSach = dr.GetString(4);
                csDto.TacGia = dr.GetString(5);
                csDto.TomTat = dr.GetString(6);
            }

            csDao.disconnect();
            return csDto;
        }

        public static CuonSachDTO getTTCuonSach(int mamuonsach)
        {
            string strQuery = "select cs.maCuonSach,cs.tinhTrang,ds.ngonNgu,ds.bia,ts.tenTuaSach,ts.tacGia,ts.tomTat ";
            strQuery += "from CuonSach cs, Muon m, ChiTietMuonSach ct, DauSach ds,TuaSach ts ";
            strQuery += " where m.maMuonSach = " + mamuonsach;
            strQuery += " and m.maMuonSach = ct.maMuonSach and ct.maCuonSach = cs.maCuonSach and cs.maDauSach = ds.maDauSach and ds.maTuaSach = ts.maTuaSach";
            CuonSachDAO csDao = new CuonSachDAO();
            IDataReader dr = csDao.executeQuery(strQuery);
            CuonSachDTO csDto = new CuonSachDTO();

            if (dr.Read())
            {
                csDto.MaCuonSach = dr.GetInt32(0);
                csDto.TinhTrang = dr.GetString(1);
                csDto.NgonNgu = dr.GetString(2);
                csDto.Bia = dr.GetString(3);
                csDto.TuaSach = dr.GetString(4);
                csDto.TacGia = dr.GetString(5);
                csDto.TomTat = dr.GetString(6);
            }

            csDao.disconnect();
            return csDto;
        }

        public bool insert(ref CuonSachDTO info)
        {
            string insertCommand = "INSERT INTO CuonSach(maDauSach,tinhTrang) VALUES( '" +
                info.MaDauSach.ToString().Trim() + "','" + info.TinhTrang + "')";

            bool flag = executeNonQuery(insertCommand);

            return flag;
        }
        public bool update(CuonSachDTO info)
        {
            string updateCommand = "UPDATE CuonSach " +
                                    "SET tinhTrang = '" + info.TinhTrang + "'" +
                                    " WHERE maCuonSach = " + info.MaCuonSach;
            bool flag = this.executeNonQuery(updateCommand);

            return flag;
        }
        public bool delete(int macuonsach)
        {
            string deleteCommand = "DELETE FROM CuonSach WHERE maCuonSach = " + macuonsach;
            bool flag = this.executeNonQuery(deleteCommand);
            return flag;
        }
        public bool kiemTraTonTai(int madausach)
        {
            string query = "select count(*) from CuonSach where maDauSach = " + madausach;
            int kt = (int)this.executeScalar(query);
            if (kt == 1)
                return true;
            else
                return false;
        }
        public bool deleteTheoNgonNgu(int madausach)
        {
            string sqlCommand = "DELETE FROM CuonSach WHERE maDauSach=" + madausach;
            bool flag = this.executeNonQuery(sqlCommand);
            return flag;
        }

        // An them vao phan sau de lam phan bao cao thong ke

        public float getTyLeMuon()
        {
            long TongSo = 0;
            long SoMuon = 0;
            float TyLe = 0;
            string strQuery = "Select count(*) from CuonSach";
            CuonSachDAO cuonsachDAO = new CuonSachDAO();
            IDataReader dr1 = cuonsachDAO.executeQuery(strQuery);
            if (dr1.Read())
                TongSo = dr1.GetInt32(0);
            string Query = "Select count(*) from CuonSach where MaCuonSach in (Select MaCuonSach from ChiTietMuonSach where MaMuonSach in (Select MaMuonSach from Muon))";
            IDataReader dr2 = cuonsachDAO.executeQuery(Query);
            if (dr2.Read())
                SoMuon = dr2.GetInt32(0);

            if (TongSo != 0)
                TyLe = SoMuon / TongSo * 100;

            cuonsachDAO.disconnect();
            return TyLe;
        }
    }
}
